﻿using System.Data;
using System.IO;
using System.Web;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace Vboot.Extend.Oa;


/// <summary>
/// TestOne主信息接口
/// </summary>
[ApiDescriptionSettings("Ext", Tag = "Test-One")]
public class OaTestOneApi : IDynamicApiController
{
    private readonly OaTestOneService _service;
    
    private readonly IHttpContextAccessor _httpContextAccessor;
    
    private readonly IWebHostEnvironment _webHostEnvironment;

    public OaTestOneApi(OaTestOneService service, 
        IHttpContextAccessor httpContextAccessor,
        IWebHostEnvironment webHostEnvironment,
        AssNumMainService numService)
    {
        _service = service;
        _httpContextAccessor = httpContextAccessor;
        _webHostEnvironment = webHostEnvironment;
    }

    /// <summary>
    /// 获取TestOne主信息的分页数据
    /// </summary>
    /// <returns></returns>
    [QueryParameters]
    public async Task<dynamic> Get(string name)
    {
        _service.JsTest();
        var pp = XreqUtil.GetPp();
        var items = await _service.repo.Context.Queryable<OaTestOne,SysOrg,SysOrg>
            ((t,o,o2)=> new JoinQueryInfos(
                JoinType.Left, o.id == t.crmid,
                JoinType.Left, o2.id == t.upmid))
            .WhereIF(!string.IsNullOrWhiteSpace(name), t => t.name.Contains(name.Trim()))
            .Select((t,o,o2) 
                => new {t.id, t.name, t.crtim, t.uptim,crman=o.name,upman=o2.name})
            .ToPageListAsync(pp.page, pp.pageSize, pp.total);
        return RestPageResult.Build(pp.total.Value, items);
    }
    
    /// <summary>
    /// 获取单个TestOne的详细信息
    /// </summary>
    /// <param name="id">TestOneID</param>
    /// <returns></returns>
    public async Task<OaTestOne> GetOne(string id)
    {
        return await _service.SingleAsync(id);
    }

    /// <summary>
    /// 新增TestOne
    /// </summary>
    public async Task<string> Post(OaTestOne main)
    {
        return await _service.InsertAsync(main);
    }

    /// <summary>
    /// 修改TestOne
    /// </summary>
    public async Task<string> Put(OaTestOne main)
    {
        return await _service.UpdateAsync(main);
    }

    /// <summary>
    /// 删除TestOne
    /// </summary>
    public async Task Delete(string ids)
    {
        await _service.DeleteAsync(ids);
    }
    
    
    
    public async Task<IActionResult> GetExp2()
    {
        string fileName = HttpUtility.UrlEncode("中国.xlsx");
        _httpContextAccessor.HttpContext.Response.Headers.Add("Access-Control-Expose-Headers", "download-filename");
        _httpContextAccessor.HttpContext.Response.Headers.Add("download-filename", fileName);
        List<UserDetails> persons = new List<UserDetails>()
            {
                new UserDetails() {ID="1001", Name="ABCD", City ="City1", Country ="USA"},
                new UserDetails() {ID="1002", Name="PQRS", City ="City2", Country="INDIA"},
                new UserDetails() {ID="1003", Name="XYZZ", City ="City3", Country="CHINA"},
                new UserDetails() {ID="1004", Name="LMNO", City ="City4", Country="UK"},
           };
        List<string> ExcelTitle = new List<string>();
        ExcelTitle.Add("aaa");
        ExcelTitle.Add("bbb");
        return await NPOIHelper<UserDetails>.ExcelDataExprot("活动管理", persons,ExcelTitle);
    }
     
     public void GetExp3()
     {
         Console.Write("basedirect:"+_webHostEnvironment.WebRootPath);
         // string path = AppDomain.CurrentDomain.BaseDirectory + "NPOIDemo.xlsx";
         string path = Path.Combine(_webHostEnvironment.WebRootPath, "NPOIDemo.xlsx");
            IWorkbook workbook = null;
            try
            {
                using (var fs = new FileStream(path, FileMode.Open, FileAccess.ReadWrite))
                {
                                    // 实例化
                if (path.IndexOf(".xlsx") > 0) // 2007
                    workbook = new XSSFWorkbook(fs);
                else if (path.IndexOf(".xls") > 0) // 2003
                    workbook = new HSSFWorkbook(fs);
                    // 查询
                    if (workbook != null)
                    {
                        ISheet sheet = workbook.GetSheetAt(0);
 
                        for (int i = 0; i < 2; i++) // 遍历每一个行,然后读取第一个单元格的内容
                        {
                            if (sheet.GetRow(i) == null || sheet.GetRow(i).GetCell(0) == null) continue;
                            Console.WriteLine(sheet.GetRow(i).GetCell(0).StringCellValue);
                        }

                        Console.WriteLine( sheet.GetRow(0).GetCell(3).NumericCellValue);
                        sheet.GetRow(0).GetCell(4).SetCellValue(6);
                        XSSFFormulaEvaluator e = new XSSFFormulaEvaluator(workbook);
                        var myCell = sheet.GetRow(0).GetCell(5);
                        myCell = e.EvaluateInCell(myCell);
                        // Console.WriteLine( sheet.GetRow(0).GetCell(5).NumericCellValue);
                        Console.WriteLine(  myCell.NumericCellValue);
                    }
                    
                }
 
                //把这个HSSFWorkbook实例写入文件
                // 增加sheet
                string xx = YitIdHelper.NextId() + "";
                workbook.CreateSheet(xx);
 
                FileStream file = new FileStream(path, FileMode.OpenOrCreate,FileAccess.ReadWrite);
 
                var sheet2 = workbook.GetSheet(xx);
                sheet2.CreateRow(0).CreateCell(0, CellType.String).SetCellValue("Sheet2Value1"); // 新增
                sheet2.GetRow(0).CreateCell(1, CellType.String).SetCellValue("Sheet2Value2"); // 新增
                sheet2.GetRow(0).CreateCell(2, CellType.String).SetCellValue("Sheet2Value3"); // 新增
                workbook.Write(file);
                file.Close();
 
                using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write, FileShare.Write))
                {
                    var value = sheet2.GetRow(0).GetCell(1).StringCellValue;
                    Console.WriteLine("shee2 row 0 cell 1 is " + value);
                    sheet2.GetRow(0).GetCell(1).SetCellValue("Sheet2Value2.2"); // 修改
 
                    sheet2.GetRow(0).RemoveCell(sheet2.GetRow(0).GetCell(2)); // 删除
 
                    workbook.Write(fs);
                    fs.Close();
                }
 
            }
            catch (Exception ex)
            {
                Console.WriteLine("出错了"+ex.Message);
            }
            Console.WriteLine("Complete");
            Console.ReadLine();

     }
    
    public async Task<FileStreamResult> GetExp()
    {
        List<UserDetails> persons = new List<UserDetails>()
            {
                new UserDetails() {ID="1001", Name="ABCD", City ="City1", Country ="USA"},
                new UserDetails() {ID="1002", Name="PQRS", City ="City2", Country="INDIA"},
                new UserDetails() {ID="1003", Name="XYZZ", City ="City3", Country="CHINA"},
                new UserDetails() {ID="1004", Name="LMNO", City ="City4", Country="UK"},
           };
 
            // Lets converts our object data to Datatable for a simplified logic.
            // Datatable is most easy way to deal with complex datatypes for easy reading and formatting.
 
            DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(persons), (typeof(DataTable)));
            // var memoryStream = new MemoryStream();
            var memoryStream = new NpoiMemoryStream();
             
            string fileName = HttpUtility.UrlEncode("中国.xlsx");
            _httpContextAccessor.HttpContext.Response.Headers.Add("Access-Control-Expose-Headers", "download-filename");
            _httpContextAccessor.HttpContext.Response.Headers.Add("download-filename", fileName);
            
            // using (var fs = new FileStream("D:\\vboot\\vboot-net\\Result2.xlsx", FileMode.Create, FileAccess.Write))
            // using (var fs = memoryStream)
            // {
                IWorkbook workbook = new XSSFWorkbook();//HSSFWorkbook 不需要用NpoiMemoryStream
                ISheet excelSheet = workbook.CreateSheet("Sheetx");
 
                List<String> columns = new List<string>();
                IRow row = excelSheet.CreateRow(0);
                int columnIndex = 0;
                
                foreach (System.Data.DataColumn column in table.Columns)
                {
                    columns.Add(column.ColumnName);
                    row.CreateCell(columnIndex).SetCellValue(column.ColumnName);
                    columnIndex++;
                }
                
                int rowIndex = 1;
                foreach (DataRow dsrow in table.Rows)
                {
                    row = excelSheet.CreateRow(rowIndex);
                    int cellIndex = 0;
                    foreach (String col in columns)
                    {
                        row.CreateCell(cellIndex).SetCellValue(dsrow[col].ToString());
                        cellIndex++;
                    }
                
                    rowIndex++;
                }
                memoryStream.AllowClose = false;
                workbook.Write(memoryStream);
                memoryStream.Flush();
                memoryStream.Seek(0, SeekOrigin.Begin);
                memoryStream.AllowClose = true;
            // }
            // memoryStream.Seek(0, SeekOrigin.Begin);
            // string filePath = "D:\\vboot\\vboot-net\\Result2.xlsx";
            // return new FileStreamResult(new FileStream(filePath, FileMode.Open), "application/octet-stream");
            // return new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            return await Task.FromResult(
                new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
            // return new FileStreamResult(memoryStream, "application/octet-stream");
           
    }
    
    public string PostImp(IFormFile file)
    {
        Console.WriteLine(file);
        DataTable dtTable = new DataTable();
        List<string> rowList = new List<string>();
        ISheet sheet;
        // using (var stream = new FileStream("D:\\vboot\\vboot-net\\Result2.xlsx", FileMode.Open))
        using (var stream = file.OpenReadStream())
        {
            stream.Position = 0;
            XSSFWorkbook xssWorkbook = new XSSFWorkbook(stream);
            sheet = xssWorkbook.GetSheetAt(0);
            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;
            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = headerRow.GetCell(j);
                if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue;
                {
                    dtTable.Columns.Add(cell.ToString());
                } 
            }
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null) continue;
                if (row.Cells.All(d => d.CellType == CellType.Blank)) continue;
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        if (!string.IsNullOrEmpty(row.GetCell(j).ToString()) && !string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))
                        {
                            rowList.Add(row.GetCell(j).ToString());
                        }
                    }
                }
                if(rowList.Count>0)
                    dtTable.Rows.Add(rowList.ToArray());
                rowList.Clear(); 
            }
        }
        return JsonConvert.SerializeObject(dtTable);
        
        
    }
    
    
}